IBM原厂资深专家：DB2优化器和成本模型分析
11月17日，IBM资深软件工程师刘俊老师在DB2用户群进行了一次“浅析DB2优化器和成本模型”的线上主题分享。小编特别整理出其中精华内容，供大家学习交流。
IBM资深软件工程师
自2005年以来一直从事DB2性能优化的产品研发，包括Visual Explain、Optimization Service Center、Optimization Expert等，在DB2查询优化和性能调优技术上具有多年实践经验
帮助IBM技术支持团队处理客户提交的DB2性能问题，利用产品功能帮助客户快速解决性能故障
目前致力于开发IBM Data Server Manager以及Optim Query(workload) Tuner相关功能和部件，包括基于查询和应用的索引顾问，以及产品自身的性能优化等
曾在developerWorks中文和英文网站发表过数据库优化相关文章
本人在多年的工作中接触过很多DB2的用户，不管是初学者或者经验丰富的，他们或多或少对DB2的行为产生过疑问，尤其是在SQL语句的执行性能方面。他们经常问的问题有：明明这条SQL很简单，只需要返回很少的记录，为什么DB2花了这么长时间？为什么这个表上明明有主键索引，但是DB2好像根本没使用它？为什么这个SQL有时候快、有时候却慢得无法忍受，而区别只是谓词上的输入参数不同？……要解答这些问题，我们首先需要了解DB2内部是如何处理SQL语句，而处理SQL语句的关键部件就是优化器。
优化器是DB2的心脏和灵魂。从功能上讲，它等同于一个专家系统，是一个标准规则集合。不管数据实际上是如何存储和操作的，DB2和SQL都可以访问该数据。从物理存储特征中分离出数据访问准则，称之为“物理数据独立性”。DB2的优化器就是实现该物理数据独立性的组件。例如，在删除一些索引的情况下，DB2仍然可以通过表扫描的方式访问到数据，尽管可能不是那么高效。又如可以在表中新增加一列，DB2仍可操作这些数据而无需更改程序的代码。所有这些成为可能都是因为对数据的物理访问路径并不是由程序员在程序中硬编码，而是由DB2自动生成。我们将数据的访问路径叫做访问计划(Access Plan)，它定义了按什么方式访问表，使用哪些索引，以及用何种连接(JOIN)方法来关联表或中间结果，最终获取到期待的结果。
DB2优化器
运行时的优化器需要根据许多信息，并包含着非常复杂的计算过程。若要使优化器的工作方式更加直观化些，可以将优化器想象成如下包含4个步骤的过程：
解析接收到的SQL语句，从语法和语义层面进行校验，确保正确的SQL输入。
分析当前的环境信息，生成最优的执行计划，其中可能也包含对原始SQL的改写。
创建计算机可读指令来执行优化的SQL。
存储它们以便后续的执行。
对于步骤2，DB2优化器是根据什么来判断SQL的最优执行计划的呢？实际上，优化器是一个基于成本的优化器(CBO)，这意味着优化器将始终尝试为查询制定最少总体成本的执行计划。要实现这个目标，DB2优化器会应用查询成本公式，该公式对每条可能的执行计划的4个因素进行评估和权衡：CPU 成本、I/O 成本、DB2 系统目录中的统计信息和实际的SQL 语句。CPU的成本还可以进行细分，包括从缓存中找到数据页的时间（Page cost）和读取一个数据记录的同时使用谓词的时间（Scan cost）。我们下面先看一个简单的例子来理解成本估算：
这个例子很简单，就是从表T1里面找到满足条件的C3的值，条件有两个：一个是C1 = 100，另一个是C1 > 10。T1这个表上有50个列，分别是C1、C2…C50。T1一共有100,000条记录，占用数据页5000。用DB2的统计数据表示就是：T1的CARD=100,000， T1的NPAGES=5000。对于这个查询中引用到的列一共有三个：C1和C2在WHERE字句里面的谓词中出现；C3在SELECT子句里出现。列出现的位置不同，对统计数据的需求也不同。通常情况下，优化器只需要谓词中出现的列的统计数据。在这个例子中，列C1一共有100个不同的值，列C2有1000个不同的值，其中次大值是1000，次小值是1。用统计数据来表示就是：C1的COLCARD=100，C2的COLCARD=1000，C2的HIGH2KEY=1000，C2的LOW2KEY=1。把这条查询格式化一下并且把统计数据都附加上去，如下：
现在按照DB2优化器的方法来进行成本估计。首先，优化器会在估计成本前产生多个访问计划。对于这条查询来说，基于用户的表和索引的定义，优化器可以选择全表扫描和索引扫描两种方法。在全表扫描的情况下，DB2需要访问这个表的所有数据页，假设访问每个数据页需要花I/O时间1ms，那么DB2总共花了5000ms把表的数据读取到缓存中。
接下来计算CPU时间。一共是5000个数据页在缓存中，当DB2做全表扫描时，CPU需要花时间从缓存中找到每一个数据页，假设每找一个花0.1ms，那么一共花了500ms的CPU时间（Page cost = 500ms）。CPU的另外一部分开销是读取一个数据记录的同时使用谓词的时间。不同类型的谓词需要的计算时间不一样，在这个例子里面，假设每个谓词的使用时间都是0.01ms，那么一共100,000记录所需要的时间就是100,000 x 2 x 0.01，一共2000ms （Scan cost = 2000ms）。从而最终估计出这条查询在使用全表查询的情况下的成本：
下面再来看在索引扫描的情况下估计的成本有何不同。首先，假设表T1有一个索引建在列C1上，索引名IX1。IX1上收集了一些基本的统计信息，例如索引的页节点占用的数据页（NLEAF）、索引的高度（NLEVEL）和索引键的不同值数（FirstKeyCard和FullKeyCard）。如下：
那么当DB2在进行索引扫描时，主要花的时间分成两个部分：一是扫描索引花的时间；二是通过索引里面存的RID（指向数据记录的指针）读取表记录的时间。索引本身存储是B+树的结构，DB2在扫描索引时是从根节点开始比较，然后一步步定位满足条件的值在哪个子树下面，最终定位到页节点。在这个过程中，DB2要访问的非叶节点数量等同于索引的高度NLEVEL。之后DB2访问页节点的数量和满足条件的记录数有关，并非所有的叶节点都需要访问，那么究竟有多少的叶节点会被读取呢？这里就必须要用到计算谓词选择性的统计数据FirstKeyCard，等同于C1的COLCARD。在这个例子中，谓词C1 = 100的选择性是指表T1中满足这个谓词条件的记录数除以T1的总记录数的比例。DB2在通常条件下会认为表的数据分布是均匀的，那么对于列C1来说，如果C1有COLCARD个不同的值，那么每种值的比例就是1/COLCARD (Filter Factor, 简称FF)，在这个例子中就是1/100，也就是说有1/100的表数据满足谓词条件。同样，在索引的叶节点里面存的RID也只需要读取1/100，也就是1/100 x NLEAF ＝ 0.5 ～ 一个索引页。好了，这样扫描索引的IO时间确定了：
在进行索引扫描时，每访问一个非页节点，都需要和里面存储的键值进行比较以确定满足条件的子树，所以也花掉一些CPU时间，估算如下：
所以扫描索引一共需要的时间是：IO cost + CPU cost = 3.22ms
第二步是通过RID读取表记录的时间。需要读取的表记录数是CARD * FF = 100,000 * 1/100 = 1000条记录，如果按照平均分布来看就是NPAGAES * FF = 5000 * 1/100 = 50个数据页，那么IO时间估算如下：
在读取表记录的同时需要使用谓词C2 > 8，需要CPU时间，估算如下：
最后，在读取表数据阶段，DB2估算的总时间为：
将两个阶段的时间累加起来就是通过索引IX1来执行SQL的总时间：3.22ms + 65ms = 68.22ms。这个时间跟全表扫描（7500ms）相比已经少了很多。所以通常情况下数据库管理员往往都给表创建一些索引来提高效率。
通过上面这个简单的例子，我们可以看出DB2的成本模型中，会对每种操作进行成本的计算，例如全表扫描、索引扫描、表连接、数据排序等等。当然这些计算公式并不是和上述例子中那样简单，DB2还会考虑更多复杂的因素。但对于很多SQL调优的案例，我们知道一个大概的成本估算公式就能进行分析了。笔者在日常工作中也积累了一些常用的技巧，在这里分享给大家。
选择索引扫描好还是全表扫描好？这个问题其实就是上面例子的扩展。我们当时在计算中使用了表和索引的统计数据，其中很关键的统计数据就是表的数据页、索引的数据页和谓词的选择性。当我们看到表的数据页很大、索引的数据页较小，并且谓词的选择性很好的时候，一般索引扫描代价更小。而相反，如果表的数据页小、索引的数据页接近、谓词选择性也不好的时候，DB2更倾向使用全表扫描。更复杂的情况下，我们还需要考虑数据预取和随机IO等，本篇将不再细述。
多索引下哪个更好？这个问题很常见。对于特定问题来说，我们会比较每个索引上的键所对应的谓词的选择性，有时候还要看谓词的类型和在索引上的使用顺序。一般的原则是，当某个索引的第一个键和谓词中某个运算符为“＝”的谓词恰好对应，并且谓词的选择性很强，甚至接近唯一，那么无疑这个索引肯定是最好的。例如上述例子中，如果C1的COLCARD接近T1的记录数，那么用C1作为第一个键的索引将是最优的。DB2在选择索引的时候还会考虑稳定性，例如同样选择性的谓词，一个运算符是“＝”，另一个是“>”，相对来说前者更加稳定，当输入参数变化时，性能相对更加一致。当然，有些情况，例如数据倾斜，需要特殊考虑。
什么情况下需要避免排序？笔者曾经遇到过好多的排序情况，因为DB2里面需要排序的地方太多了，例如SQL里面加上的DINSTINCT，ORDER BY，GROUP BY关键字，还有表连接过程中需要的，例如合并连接（Merge Scan Join）等。并不是所有情况下排序都不好，很简单的例子就是通过一个主键索引访问数据，但是需要排序的列不在索引键内。这种情况即使把这个列加入到索引键内，作用也是非常小的，因为DB2通过主键扫描后，最后返回的记录数最多只能是一条，根本不需要排序。而真正需要避免排序的情况是要处理的集非常大、代价高的情况。笔者之前遇到的一个例子就是两个非常大的表进行连接，连接完了需要做DISTINCT，形成了一个巨大的排序。最终，笔者在检查了连接条件后，把DISTINCT下放到每个表上面，然后创建合适的索引，从而最终避免了排序。
嵌套循环连接(Nested Loop Join)有什么地方需要重点注意？嵌套循环连接无疑是DB2中最常见的表连接方式。它的原理也很简单，就是先扫描一张表，获得所有满足条件的记录，然后对每一条记录再根据连接条件访问第二张表，这样经过对第二张表多次扫描后，获取第二张表上满足条件的记录，最后合并在一起。其中最关键的地方是第一张表满足条件的记录数和第二张表的访问方式。一般来说，如果第一张表满足条件的记录数很大，并且第二张表的访问方式不好，例如是全表查询，那么必须得重点注意。但我们在访问计划图上却很少见到这种情况，因为DB2已经通过统计数据进行了计算，发现这种情况的代价很大，从而选择了其他的表连接方法。往往我们最常见的情况是第一张表满足条件的记录数很小，同时第二张表的访问方式是全表扫描。这时我们仍然要重点注意，因为有些情况下DB2可能错误的估计了第一张表满足条件的记录数，最常见的原因有统计数据缺失、谓词类型难以估计选择性等。所以这种情况下需要把查询拆分，在实际环境中运行，看看第一张表实际满足条件的记录数到底是多少，如果和DB2估算的有很大差异，必须要查明原因再想办法解决。
到此，让我们再回头看文章开头的那些问题，是不是都能迎刃而解了呢？
最后给大家推荐三篇文章：
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1006optimquerytuner1/
http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1105optimquerytuner2/
http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1207optimquerytuner3/
Q1：问个nlj的问题，如果只看qualified row的话，到底是大表放前面好，还是小表？
A：真实的情况是都有可能，因为DB2是cost model，所以它必须看后面的表是否有好的访问方式。
Q2：请问db2有无第三方工具软件可以直接跟踪到最耗资源的sql语句，比如Oracle有类似的toad sql？
A：IBM的Data Server Manger可以，有各种监控的配置和告警，现在有免费版可以用。
Q3：案例各种操作的代价即假设的0.01ms 0.1ms这些在db2内部是否有参数控制？这些预设的代价如果不合理会影响成本的估算？
A：这是个好问题，我的理解是有预设值，而且每个版本还会变化，否则怎么能跟上硬件性能的提升呢？例子里面的这些时间只是为了方便理解写的，确实和硬件性能有关。有群友刚才说了用MIPS来换算（CPU cost的计算的时候应该计算MIPS，计算用了多少条计算机指令，这样就不依赖于具体CPU了）。在真实的调优过程中，基本上不会列准确的公式计算，都是估算。
Q4：请问，如果SQL中的表，数据经常发生变化，对执行计划有什么影响？怎样做才能获得最优的执行计划？可能是全表load覆盖，也可能是增量增长，比如用户的话单表，GSM、GPRS话单，每月的数据量都在增长，日表每天更新，月表每月更新。
A：可以在表上加volatile参数，表示这个表的数据经常有大起大落的变化。表上加上volatile后，DB2会优先选择索引扫描，大多数用户都有定期的RUNSTATS，REORG来维护，这样来保证统计信息的准确性。
Q5：表连接比较多的时候怎么选择一个合理的连接顺序？
A：这个问题很好。可以从两个方面来看：一个是从现有的连接顺序。既然这个顺序是DB2选的，那么肯定有它的理由，前提是它的估算是准的，然而很多情况下估算并不准，就会导致一些问题，那么我们就需要找的可能产生问题的地方。第二个方面是从查询本身来看，在表和表的连接关系已经清楚的情况下，哪个表可以作为第一个表，哪个表是第二个，如果需要支持这样的连接顺序，是否需要创建新的索引或者修改已有的索引，需要一步步来推导。
Q6：优化器在处理一个字段上只有2个值的索引机制是怎么样的？
A：一般不建议在colcard少的字段上创建索引，效率不高。除非是数据有倾斜，而大部分查询都是返回少量记录这种。例如sex字段，如果1%是male，而查询谓词中都是sex='m'这种。
Q7：db2建索引有什么最佳实践？
A：建索引应少而精，应用全局考虑。DB2有专用的EXPLAIN MODE可以在compile的同时建议合适的索引。某些情况下，针对于单条查询，有可能会推荐数量较多，有些是相似的。此时索引优化应该从全局考虑。
Q8：想问下关于dpf数据库：在什么情况下，应该使用联合分布键？以及联合分布键的Hash值，是基于多个字段生成的吗？
A：DPF的distribution key的调优用的不多，我的理解是根据应用来选择，如果应用的很多基于多个column的谓词，形成了一种模式，那么应该作为联合分布键。
Q9：当多表关联或临时表关联，谓词无法带入，无法判断柱状图的时候有什么好办法？
A：对于连接谓词的选择性，DB2的公示也不是很准。很多情况下需要借助于Statistics view这个东西来帮忙，具体参考以下链接文章：
www.ibm.com/developerworks/cn/data/library/techarticle/dm-1305leverage/
“DBA+社群”将陆续在各大城市群进行线上专题分享活动，以后每周一、周三晚上为【DBA+专业群】的固定时间，每周二、周四晚上为【DBA+各城市群】的固定时间，每周五晚上为【DAMS架构师精英群】的固定时间，欢迎大家积极加入我们。无论是内容还是形式，有好的建议我们都会积极采纳。
未入群的小伙伴们可先关注DBA+社群微信公众号：dbaplus，回复“我要入群”获得各城市群二维码，再加入你所在的城市微群。
小编精心为大家挑选了近日最受欢迎的几篇热文，回复001，看丁俊的《【重磅干货】看了此文，Oracle SQL优化文章不必再看！》；
回复002，看《灾备故障上了红头文件，容灾技术到底哪家强？》；
回复003，看吕海波的《去不去O，谁说了算？》；
回复004，看胡怡文《PG，一道横跨oltp到olap的梦想之桥》；
回复005，看付新《达梦专家解读：国产数据库也疯狂》。
DBA+社群，是一个以连接全中国DBA为使命的公益性组织，致力于成为DBA界的UBER，充分利用DBA的业余时间，为广大DBA搭建一个学习交流、价值实现、人脉搭建、跨界合作的平台。目前，DBA+社群已于北上广深等全国15大城市成立DBA+城市群，100+业界专家成为联合发起人，6000+跨界DBA加入队伍，每周4次线上技术分享活动同步直播。
扫码关注
DBAplus社群
超越DBA圈子，连接的不仅仅是DBA